﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.IO.Ports;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Threading;

namespace smartGardenForm
{
    public class dataDeal
    {
        //从配置文件中获取连接数据库字符串
        string sqlConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["smartGardenConnectinString"].ConnectionString;
        //插入数据库

        string manFromSendOrderController;
        static int findModeIdCount;
        string[] valueMan = new string[5];
        //  dataDeal manDataDealSerialPort = new dataDeal();

        //插入数据库语句
        public bool insertDb(string sqlComm)
        {
            //MessageBox.Show(sqlComm);
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            try
            {
                conn.Open();
            }
            catch
            {
                MessageBox.Show("数据库打开失败！");
            }
            SqlCommand sqlCommand = new SqlCommand(sqlComm, conn);
            try
            {
                sqlCommand.ExecuteNonQuery();
                conn.Close();
            }
            catch
            {
                //MessageBox.Show(ex.ToString());
                //MessageBox.Show(msg);
                return false;
            }
            return true;
        }

        #region 查询对应设备编号对应的名称 1018
        /// <summary>
        /// 查询对应设备编号对应的名称
        /// </summary>
        /// <param name="facNo">设备编号  M1</param>
        /// <returns></returns>
        public string facNoToFacName(string facNo)
        {
            string sql = "select facilityName from facility where facilityNo = '" + facNo + "'";

            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();                      //执行数据库查询语句并存储返回数据            
            string facilityName = "";
            while (sth.Read())                                                //读取数据信息                
            {
                facilityName = sth["facilityName"].ToString();                //设备名称
            }
            sqlconn.Close();
            return facilityName;
        }
        #endregion

        #region 查询对应设备编号对应的开启状态 1018
        /// <summary>
        /// 查询对应设备编号对应的开启状态
        /// </summary>
        /// <param name="modeId">模式Id</param>
        /// <param name="area">区域号</param>
        /// <param name="facNo">设备号  M1</param>
        /// <returns></returns>
        public string facNoToFacState(string modeId, string area, string facNo)
        {
            string sql = "select state from modeFacility where facility = '" + facNo + "' and modeId = '" + modeId + "' and area = '" + area + "'";

            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据            
            string facState = "";
            while (sth.Read())                                  //读取数据信息                
            {
                facState = sth["state"].ToString();             //设备开启状况 0/1
            }
            sqlconn.Close();
            //MessageBox.Show(sql);
            return facState;
        }
        #endregion

        #region 查询对应模式下区域的所有设备编号及名称 1018
        /// <summary>
        /// 查询对应模式下区域的所有设备编号及名称
        /// </summary>
        /// <param name="modeId">模式id</param>
        /// <param name="area">区域号</param>
        /// <returns></returns>
        public string[] chkModeFac(string modeId, string area)
        {
            //查询对应模式下区域的所有设备编号及名称
            string sql = "select facility,facilityName from view_modeFac where modeId = '" + modeId + "' and area = '" + area + "'";
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();
            ArrayList temp = new ArrayList();
            while (sth.Read())
            {
                temp.Add(sth["facilityName"] + "+" + sth["facility"]);
            }

            string[] facs = new string[temp.Count];
            for (int i = 0; i < temp.Count; i++)
            {
                facs[i] = temp[i].ToString();
            }
            return facs;
        }
        #endregion

        //修改数据库语句
        public void updateDb(string state, string modeId, string area, string facNo)
        {
            string sqlComm = "update modeFacility set state = '" + state + "' where modeId = '" + modeId + "' and area = '" + area + "' and facility = '" + facNo + "'";
            //MessageBox.Show(sqlComm);
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand command = new SqlCommand(sqlComm, conn);
            command.ExecuteNonQuery();
            conn.Close();
        }

        //查询数据最后一次数据,返回一维数组
        public int[] selectData(string modeId, int area)
        {
            string sql = "SELECT TOP (1) * FROM dealReceive  WHERE area = '" + area + "' and modeId = '" + modeId + "' ORDER BY ID DESC";
            //MessageBox.Show(sql);
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            try
            {
                sqlconn.Open();
            }
            catch
            {
                MessageBox.Show("未能打开数据库！");
            }
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据            
            int[] newData = new int[8];
            while (sth.Read())                                  //读取数据信息                
            {
                //Thread.Sleep(1000);
                newData[0] = (int)sth["temperature"];           //采集对应区域最后一次温度数据
                newData[1] = (int)sth["humidity"];              //空气湿度
                newData[2] = (int)sth["illumination"];          //光照强度
                newData[3] = (int)sth["rain"];                  //天气
                newData[4] = (int)sth["soilHum"];               //土壤湿度
                newData[5] = (int)sth["facilitiesNo"];          //设备号
                newData[6] = (int)sth["receiveTime"];           //采集时间
                newData[7] = (int)sth["modeId"];                //模式编号
                //MessageBox.Show(newData[7].ToString());
            }
            sqlconn.Close();
            return newData;
        }

        //查询数据最后一次数据,返回一维数组
        public int[] selectAutoValue(string modeId, int area)
        {
            string sql = "select TOP (1) * from autoModeValue where area = '" + area + "'and modeId = '" + modeId + "' order by id desc";
            //MessageBox.Show(sql);
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            try
            {
                sqlconn.Open();
            }
            catch
            {
                MessageBox.Show("未能打开数据库！");
            }
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据            
            int[] autoValue = new int[7];
            while (sth.Read())                                  //读取数据信息                
            {
                //Thread.Sleep(1000);
                autoValue[0] = (int)sth["tempMin"];           //采集对应区域最后一次温度数据
                autoValue[1] = (int)sth["tempMax"];              //空气湿度
                autoValue[2] = (int)sth["illMin"];          //光照强度
                autoValue[3] = (int)sth["illMax"];                  //天气
                autoValue[4] = (int)sth["soilHumMin"];               //土壤湿度
                autoValue[5] = (int)sth["soilHumMax"];          //设备号
                //autoValue[6] = (int)sth["setTime"];           //采集时间
                //MessageBox.Show(autoValue[2].ToString());
            }
            sqlconn.Close();
            return autoValue;
        }

        //查询区域号区域号下拉框自动 添加区域号
        public bool selectArea(string modeId, ComboBox comboBoxarea)
        {
            //string sql = "select area from modeArea where modeId = '" + modeId + "'";
            string sql = "select distinct area from dealReceive where modeId = '" + modeId + "' order by area";
            //MessageBox.Show(sql);
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            try
            {
                sqlconn.Open();
            }
            catch
            {
                MessageBox.Show("数据库打开失败！sqlConnStr:" + sqlConnStr);
                return false;
            }
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();
            while (sth.Read())
            {
                comboBoxarea.Items.Add(sth["area"].ToString());
            }
            comboBoxarea.SelectedIndex = 0;
            sqlconn.Close();
            return true;
        }

        //动态添加模式名称
        public void modeNameAdd(ComboBox comboBoxModeName)
        {
            string sql = "select modeName from mode";
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            sqlconn.Open();
            SqlCommand comm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = comm.ExecuteReader();
            while (sth.Read())
            {
                if (sth["modeName"].ToString().Trim() != "自定义".ToString())
                {
                    comboBoxModeName.Items.Add(sth["modeName"].ToString());
                }
            }
            sqlconn.Close();
            comboBoxModeName.SelectedIndex = 0;
        }

        //判断区域号是否存在，存在则插入，不存在则不插入
        public void insertArea(string modeId, string area, string facilitiesNo)
        {
            int areaRece = int.Parse(area);
            int facNoRece = int.Parse(facilitiesNo);
            string sql = "SELECT distinct area from modeArea where modeId = '" + modeId + "'";
            string sqlConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["smartGardenConnectinString"].ConnectionString;
            SqlConnection conn = new SqlConnection(sqlConnStr);
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "area");
            int count = ds.Tables["area"].Rows.Count;
            int[] areaFromDb = new int[count];
            int[] facNoFromDb = new int[count];
            int i = 0;
            int temp = 0;
            foreach (DataRow row in ds.Tables["area"].Rows)
            {
                areaFromDb[i] = int.Parse(row[0].ToString());
                facNoFromDb[i] = int.Parse(row[1].ToString());
                i++;
            }
            for (int j = 0; j < i; j++)
            {
                if (areaFromDb[j] == areaRece)
                {
                    if (facNoFromDb[j] == facNoRece)
                    {
                        temp = 1;
                        break;
                    }
                }
                else
                {
                    temp = 0;
                }
            }
            if (temp == 0)
            {
                sql = "insert into modeArea(modeId,area) values (" + modeId + "," + area + ") ";
                insertDb(sql);
            }
            conn.Close();
        }

        //查询对应区域自动模式下 设定的 范围值
        public int[] selectRang(string modeId, int area)
        {
            string sql = "SELECT TOP (1) * from autoModeValue where area =  " + area + "ORDER BY ID DESC";
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            try
            {
                sqlconn.Open();
            }
            catch
            {
                MessageBox.Show("未能打开数据库！");
            }
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据            
            int[] value = new int[8];
            while (sth.Read())                                  //读取数据信息                
            {
                //Thread.Sleep(1000);
                value[0] = (int)sth["tempMin"];
                value[1] = (int)sth["tempMax"];
                value[2] = (int)sth["illMin"];
                value[3] = (int)sth["illMax"];
                value[4] = (int)sth["soilHumMin"];
                value[5] = (int)sth["soilHumMax"];
            }
            sqlconn.Close();
            return value;
        }

        //查询模式对应区域所有已开启设备
        public string[] queryOpenFac(string modeId, string area)
        {
            string sql = "select facility from modeFacility where modeId = '" + modeId + "' and area = '" + area + "' and state = '1'";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataReader sth = comm.ExecuteReader();
            ArrayList temp = new ArrayList();
            while (sth.Read())
            {
                temp.Add(sth["facility"]);
            }
            string[] openFac = new string[temp.Count];
            for (int i = 0; i < temp.Count; i++)
            {
                openFac[i] = temp[i].ToString();
                //MessageBox.Show(openFac[i].ToString());
            }
            //MessageBox.Show(sql);
            conn.Close();
            return openFac;
        }

        //自动控制模式下扫描数据库数据 最新操作指令为 自动模式AUTO 是 0/1 关/开
        //参数：PC/APP/WEB
        public string[] selectComm(string modeId, string area, string from)
        {
            string sql = "select TOP (1)orderFrom,area,orderSend from actionInstruction where area = '" + area + "' and modeId = '" + modeId + "' and orderFrom = '" + from + "' and orderSend like '%AUTO%' order by id desc";
            //string sql = "select TOP (1)orderFrom,area,orderSend from actionInstruction where orderFrom = '" + from + "' and orderSend like '%AUTO%' order by id desc";
            //MessageBox.Show(sql);
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            try
            {
                sqlconn.Open();
                SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
                SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据            
                string[] value = new string[3];
                while (sth.Read())                                  //读取数据信息                
                {
                    value[0] = sth["orderFrom"].ToString();
                    value[1] = sth["area"].ToString();
                    value[2] = sth["orderSend"].ToString();
                }
                sqlconn.Close();
                return value;
            }
            catch
            {
                //MessageBox.Show("444未能打开数据库！sqlConnStr:" + sqlConnStr);
                //throw;
            }
            return null;
        }
        /**********手动模式下扫描来自哪一端的操作：PC,WEB,APP 20140830**********/
        public string[] chkManFrom(string orderFrom)
        {

            string sqlchkmanfrom = "select top(1) * from actionInstruction where mode='MAN' and orderFrom = '" + orderFrom + "' and ordersend like 'M%' order by actionTime desc ";
            // MessageBox.Show(sqlchkmanfrom);
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            try
            {
                sqlconn.Open();
            }
            catch
            {
                MessageBox.Show("未能打开数据库！");
            }
            SqlCommand sqlcomm = new SqlCommand(sqlchkmanfrom, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据   


            while (sth.Read())                                  //读取数据信息                
            {
                valueMan[0] = sth["orderFrom"].ToString();
                valueMan[1] = sth["area"].ToString();
                valueMan[2] = sth["facilitiesNo"].ToString();
                valueMan[3] = sth["mode"].ToString();
                valueMan[4] = sth["orderSend"].ToString();
            }
            if (int.Parse(valueMan[1]) < 10)
            {
                valueMan[1] = "0" + valueMan[1];
            }
            if (int.Parse(valueMan[2]) < 10)
            {
                valueMan[2] = "0" + valueMan[2];
            }
            sqlconn.Close();
            return valueMan;
        }

        /**********手动模式下扫描数据库.20140829**********/
        public string manFromSendOrder(string orderFrom)
        {
            string[] saveValue = new string[5];
            string sqlchkmanfrom = "select top(1) * from actionInstruction where mode='MAN' and orderFrom = '" + orderFrom + "' and ordersend like 'M%' order by id desc ";
            //MessageBox.Show("查询最新一条记录"+sqlchkmanfrom);
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            try
            {
                sqlconn.Open();
            }
            catch
            {
                //MessageBox.Show("666未能打开数据库！sqlConnStr:" + sqlConnStr);
                throw;
            }
            SqlCommand sqlcomm = new SqlCommand(sqlchkmanfrom, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据  


            while (sth.Read())                                  //读取数据信息                
            {
                saveValue[0] = sth["orderFrom"].ToString();
                saveValue[1] = sth["area"].ToString();
                saveValue[2] = sth["facilitiesNo"].ToString();
                saveValue[3] = sth["mode"].ToString();
                saveValue[4] = sth["orderSend"].ToString();
            }
            if (int.Parse(saveValue[1]) < 10)
            {
                saveValue[1] = "0" + saveValue[1];
            }
            if (int.Parse(saveValue[2]) < 10)
            {
                saveValue[2] = "0" + saveValue[2];
            }

            sqlconn.Close();
            manFromSendOrderController = "AAE" + saveValue[1] + "F01" + saveValue[4] + "55";
            //MessageBox.Show(manFromSendOrderController);
            return manFromSendOrderController;
        }

        //判断输入对应模式下对应区域号   处于 哪一端哪一种模式控制
        //返回 string  如：网页开启手动模式
        public string JudgeAreaAutoMode(int area, string modeId)
        {
            string sql = "select TOP(1) orderSend,orderFrom from actionInstruction where area  = '" + area + "'and modeId = '" + modeId + "' and instruction like '%端%'order by id desc";
            //MessageBox.Show(sql);
            string orderSend = "";
            int autoModeSwitch;
            string orderFrom = "", side = "";
            string state = "";
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            try
            {
                sqlconn.Open();
            }
            catch
            {
                MessageBox.Show("未能打开数据库！");
            }
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据            
            while (sth.Read())                                  //读取数据信息                
            {
                orderSend = sth["orderSend"].ToString();
                orderFrom = sth["orderFrom"].ToString();
            }
            sqlconn.Close();
            try
            {
                autoModeSwitch = int.Parse(orderSend.Substring(orderSend.Count() - 1, 1));
                switch (orderSend.Substring(orderSend.Count() - 2, 1))
                {
                    case "O": state = "开启自动模式"; break;
                    case "N": state = "开启手动模式"; break;
                }
            }
            catch
            {
                return "-";
            }
            if (autoModeSwitch == 1)
            {
                switch (orderFrom)
                {
                    case "PC": side = "客户端"; break;
                    case "APP": side = "手机端"; break;
                    case "WEB": side = "网页"; break;
                }
                string msg = side + state;
                return msg;
            }
            return "-";
        }

        //判断手动模式下下拉框区域对应各机械设备 最新开启状态
        public string[] chkFacState(string modeId, int area)
        {
            string[] state = new string[5];
            state[0] = chkFacFile(modeId, "M1", area);       //风扇
            state[1] = chkFacFile(modeId, "M2", area);       //加热灯
            state[2] = chkFacFile(modeId, "M3", area);       //水泵
            state[3] = chkFacFile(modeId, "M4", area);       //天窗
            state[4] = chkFacFile(modeId, "M5", area);       //白炽灯
            for (int i = 0; i < 5; i++)
            {
                if (state[i] == "0")
                {
                    state[i] = "关";
                }
                else
                {
                    state[i] = "开";
                }
            }
            return state;
        }

        //facility 为机械设备编号M1/M2/M3...  风机、水泵、步进电机等
        //返回：设备字段  0
        public string chkFacFile(string modeId, string facility, int area)
        {
            //string selectArea = area.ToString();
            //string sql = "select TOP (1) orderSend from actionInstruction where orderSend like '%M" + facNo + "%' and area = " + selectArea + "and modeId = '" + modeId + "' order by id desc";
            string sql = "select state from modeFacility where modeId = '" + modeId + "'and area ='" + area + "'and facility = '" + facility + "'";
            //MessageBox.Show(sql);
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            try
            {
                sqlconn.Open();
            }
            catch
            {
                MessageBox.Show("未能打开数据库！");
            }
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据            
            string value = "";
            while (sth.Read())                                  //读取数据信息                
            {
                value = sth["state"].ToString();
            }
            sqlconn.Close();
            return value;
        }

        //查询所有模式号
        public string[] modeId()
        {
            string sql = "SELECT modeId  from mode";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataReader sth = comm.ExecuteReader();
            ArrayList temp = new ArrayList();
            while (sth.Read())
            {
                temp.Add(sth["modeId"]);
            }
            string[] modeId = new string[modeIdCount()];
            for (int i = 0; i < modeIdCount(); i++)
            {
                modeId[i] = temp[i].ToString();
            }
            conn.Close();
            return modeId;
        }

        //查询对应模式下所有区域号
        public string[] modeArea(string modeId)
        {
            try
            {
                string sql = "SELECT area  from modeArea where modeId = '" + modeId + "'";
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = sqlConnStr;
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                SqlDataReader sth = comm.ExecuteReader();
                ArrayList temp = new ArrayList();
                while (sth.Read())
                {
                    temp.Add(sth["area"]);
                }
                string[] modeAreaArr = new string[modeIdCount()];
                for (int i = 0; i < temp.Count; i++)
                {
                    modeAreaArr[i] = temp[i].ToString();
                }
                conn.Close();
                return modeAreaArr;
            }
            catch
            { }
            return null;
        }

        //统计共有多少个模式
        public int modeIdCount()
        {
            string sql = "SELECT count(*)as count from mode";
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = sqlConnStr;
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                SqlDataReader sth = comm.ExecuteReader();
                while (sth.Read())
                {
                    findModeIdCount = (int)sth["count"];
                }
                //MessageBox.Show(findModeIdCount.ToString());
                conn.Close();
                return findModeIdCount;
            }
            catch
            { ; }
            return 0;
        }

        //通过modeid 查找 模式名称
        public string modeIdToModeId(string modeId)
        {
            string modeName = "";
            string sql = "SELECT modeName from mode where modeId = '" + modeId + "'";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand command = new SqlCommand(sql, conn);
            SqlDataReader sth = command.ExecuteReader();
            while (sth.Read())
            {
                modeName = sth["modeName"].ToString();
            }
            conn.Close();
            return modeName;
        }

        //通过模式名称 查找 模式id
        public string modeNameToModeId(string modeName)
        {
            string modeId = "";
            string sql = "SELECT modeId from mode where modeName like '%" + modeName + "%'";
            //MessageBox.Show(sql);
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand command = new SqlCommand(sql, conn);
            SqlDataReader sth = command.ExecuteReader();
            while (sth.Read())
            {
                modeId = sth["modeId"].ToString();
            }
            conn.Close();
            return modeId;
        }

        //根据模式判断模式下应有的 设备 M1、M2、M3...
        //返回  1、2、3、、、、
        public int[] chkModeFac(string modeIdchk)
        {
            /*查找对应模式下对应区域存有设备*/
            string sql = "select distinct facilityNo from facility f ,modeFacility m where m.facility = f.facilityNo and m.modeId = '" + modeIdchk + "'";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataReader sth = comm.ExecuteReader();
            ArrayList temp = new ArrayList();
            while (sth.Read())
            {
                temp.Add(sth["facilityNo"]);
            }
            int[] modeFac = new int[temp.Count];
            for (int i = 0; i < temp.Count; i++)
            {
                modeFac[i] = int.Parse(temp[i].ToString().Substring(1, 1));
            }
            conn.Close();
            return modeFac;
        }

        //根据模式判断模式下应有的
        //监控数据  如：温度、湿度等
        public int[] chkModeSensor(string modeIdchk)
        {
            /*查找对应模式下对应区域存有设备*/
            string sql = "select sensor from modeSensor where modeId = '" + modeIdchk + "'";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataReader sth = comm.ExecuteReader();
            ArrayList temp = new ArrayList();
            while (sth.Read())
            {
                temp.Add(sth["sensor"]);
            }
            int[] modeSensor = new int[temp.Count];
            for (int i = 0; i < temp.Count; i++)
            {
                modeSensor[i] = int.Parse(temp[i].ToString().Substring(1, 1));
            }
            conn.Close();
            return modeSensor;
        }



        //对应模式下对应区域的数据
        //参数：模式号；区域号
        public string[] modeAreaDataDisplay(string modeId, string area)
        {
            string sql = "select top(1) * from dealReceive where modeId = '" + modeId + "' and area = '" + area + "' order by id desc";
            //MessageBox.Show(sql);
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand sqlcomm = new SqlCommand(sql, conn);
            SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据            
            string[] dataDisplay = new string[5];
            while (sth.Read())                                  //读取数据信息                
            {
                //Thread.Sleep(1000);
                dataDisplay[0] = modeIdToModeId(sth["modeId"].ToString());        //模式名称
                dataDisplay[1] = sth["area"].ToString();                          //区域号
                dataDisplay[2] = sth["temperature"].ToString();                   //对应模式下对应区域的温度
                dataDisplay[3] = sth["illumination"].ToString();                  //对应模式下对应区域的光照强度
                dataDisplay[4] = sth["soilHum"].ToString();                       //对应模式下对应区域的土壤湿度
                //autoValue[6] = (int)sth["setTime"];                             //采集时间
                //MessageBox.Show(autoValue[2].ToString());
            }
            conn.Close();
            return dataDisplay;
        }

    }

    public class autoMode
    {
        public void chkMode()
        {
            dataDeal dealData = new dataDeal();
            int modeIdCount;
            string modeId;
            string[] webAutoState = new string[3];
            string[] appAutoState = new string[3];
            string[] pcAutoState = new string[3];
            string webOrderFrom, webArea, webOrderSend;
            string appOrderFrom, appArea, appOrderSend;
            string pcOrderFrom, pcArea, pcOrderSend;
            string webAutoSwitch, appAutoSwitch, pcAutoSwitch;
            while (true)
            {
                try
                {
                    //Thread.Sleep(1000);
                    //获取区域模式总数，并定义有总数的字符串数组
                    modeIdCount = dealData.modeIdCount();
                    string[] modeIdArr = dealData.modeId();
                    string[] modeAreaArr;
                    string modeArea;

                    //有多少个模式，就循环查询wap app pc端最新自动指令
                    for (int j = 0; j < modeIdCount; j++)
                    {
                        modeId = modeIdArr[j];
                        //MessageBox.Show(j.ToString());
                        modeAreaArr = dealData.modeArea(modeId);
                        for (int i = 0; i < modeAreaArr.Length; i++)
                        {
                            modeArea = modeAreaArr[i];
                            if (modeArea != null)
                            {
                                //MessageBox.Show("modeid:" + modeId + "\nmodeArea:" + modeArea);
                                //查看对应用户端，自动模式开/关
                                webAutoState = dealData.selectComm(modeId, modeArea, "WEB");
                                appAutoState = dealData.selectComm(modeId, modeArea, "APP");
                                pcAutoState = dealData.selectComm(modeId, modeArea, "PC");

                                //若对应区域没有自动模式指令，则跳过

                                //操作指令来自web
                                webOrderFrom = webAutoState[0];
                                //web最新自动模式 区域
                                webArea = webAutoState[1];
                                //发送的指令  AUTO0/1
                                webOrderSend = webAutoState[2];

                                appOrderFrom = appAutoState[0];
                                appArea = appAutoState[1];
                                appOrderSend = appAutoState[2];

                                pcOrderFrom = pcAutoState[0];
                                pcArea = pcAutoState[1];
                                pcOrderSend = pcAutoState[2];

                                //释放数组
                                appAutoState = null;
                                webAutoState = null;
                                pcAutoState = null;

                                //web自动模式 判断0/1
                                //判断指令WEBAUTO 0/1 是开还是关
                                try
                                {
                                    webAutoSwitch = webOrderSend.Substring(webOrderSend.Count() - 1, 1);
                                    if (int.Parse(webAutoSwitch) == 1)
                                    {
                                        //调用自动函数，范围判断，发送指令
                                        autoModeCompare(modeId, "web", int.Parse(webArea));
                                    }
                                }
                                catch
                                {
                                    ;
                                }
                                try
                                {
                                    appAutoSwitch = appOrderSend.Substring(appOrderSend.Count() - 1, 1);
                                    if (int.Parse(appAutoSwitch) == 1)
                                    {
                                        autoModeCompare(modeId, "app", int.Parse(appArea));
                                    }
                                }
                                catch
                                {
                                    ;
                                }
                                try
                                {
                                    pcAutoSwitch = pcOrderSend.Substring(pcOrderSend.Count() - 1, 1);
                                    if (int.Parse(pcAutoSwitch) == 1)
                                    {
                                        autoModeCompare(modeId, "pc", int.Parse(pcArea));
                                    }
                                }
                                catch
                                {
                                    ;
                                }
                                //MessageBox.Show("modeId:" + modeId + "\nmodeArea:" + modeArea + "\npcAuto:" + pcAutoSwitch);
                            }
                        }

                    }
                }
                catch { }

            }
        }

        //自动模式下，将设定值和采集到最新数据进行比较
        //serialPort.sendOrder() 拼接发送指令
        public void autoModeCompare(string modeId, string from, int area)
        {
            //MessageBox.Show("modeid:" + modeId + "\nmodeArea:" + area);
            //MessageBox.Show(area.ToString());
            //MessageBox.Show("自动模式来自： " + from);
            dataDeal mydealDataChk = new dataDeal();
            serialPort myserialPortSend = new serialPort();

            int temp, hum, ill, soilHum, rain, facNo;
            int tempMin, tempMax, illMin, illMax, soilHumMin, soilHumMax;
            //string tempMsg, humMsg, illMsg, soilHumMsg;                 //消息变量，调试用

            int[] lastData = mydealDataChk.selectData(modeId, area);     //调用函数.读取下位机采取对应区域的最新数据
            int[] dataSet = mydealDataChk.selectRang(modeId, area);      //调用函数.读取自动控制设定控制范围值
            temp = lastData[0];
            hum = lastData[1];
            ill = lastData[2];
            rain = lastData[3];
            soilHum = lastData[4];
            facNo = lastData[5];
            lastData = null;

            tempMin = dataSet[0];
            tempMax = dataSet[1];
            illMin = dataSet[2];
            illMax = dataSet[3];
            soilHumMin = dataSet[4];
            soilHumMax = dataSet[5];
            dataSet = null;

            if (modeId == "1")
            {
                //MessageBox.Show("modeId1\nautoModeCompare");

                //土壤湿度判断
                if (soilHum > soilHumMax)
                {
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M11", "开启风机", facNo.ToString());
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M30", "关闭水泵", facNo.ToString());
                }
                if (soilHum < soilHumMin)
                {
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M31", "开启水泵", facNo.ToString());
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M10", "关闭风机", facNo.ToString());
                }
                if (soilHum >= soilHumMin && soilHum <= soilHumMax)
                {
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M30", "关闭水泵", facNo.ToString());
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M10", "关闭风机", facNo.ToString());
                }
            }

            if (modeId == "2")
            {
                //MessageBox.Show("modeId2\nautoModeCompare");

                //土壤湿度判断               
                if (soilHum < soilHumMin)
                {
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M31", "开启水泵", facNo.ToString());
                }
                if (soilHum >= soilHumMin && soilHum <= soilHumMax)
                {
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M30", "关闭水泵", facNo.ToString());
                }
            }

            if (modeId == "3")
            {
                //温度判断
                if (temp > tempMax)
                {
                    //tempMsg = "，温度过高,喷水灌溉开！\n";
                    //参数说明：area 区域， command 控制指令, instruction 指令说明 , facNO 设备号(设为空，以后方便修改)
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M31", "开启水泵", facNo.ToString());
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M20", "关闭加热灯", facNo.ToString());
                }
                if (temp < tempMin)
                {
                    //tempMsg = "，温度过低，加热灯开！\n";
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M21", "开启加热灯", facNo.ToString());
                    //myserialPortSend.buildOrder(modeId,from, area.ToString(), "M30", "关闭水泵", facNo.ToString());
                }
                if (temp >= tempMin && temp <= tempMax)
                {
                    //tempMsg = "，温度正常如果土壤湿度低，喷水开，否则关闭喷水、关闭加热灯！\n";     
                    if (soilHum < soilHumMin)
                    {
                        //tempMsg = "，温度正常而且土壤湿度过低时，喷水灌溉开！\n";               
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M31", "开启水泵", facNo.ToString());
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M20", "关闭加热灯", facNo.ToString());
                    }
                    else
                    {
                        //tempMsg = "，温度正常，加热灯关！,喷水灌溉关！\n";
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M30", "关闭水泵", facNo.ToString());
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M20", "关闭加热灯", facNo.ToString());
                    }
                }
                //光照强度判断
                if (ill > illMax || (ill >= illMin && ill <= illMax))
                {
                    //illMsg = "，光照过强或正常，关天窗!\n";
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M40", "关闭天窗", facNo.ToString());
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M50", "关闭白炽灯", facNo.ToString());
                }
                if (ill < illMin)
                {
                    if (rain == 1)
                    {
                        //illMsg = "，光照过弱，下雨，开白炽灯!\n";
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M51", "开启白炽灯", facNo.ToString());
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M40", "关闭天窗", facNo.ToString());
                    }
                    else
                    {
                        //illMsg = "，光照过弱，没下雨，开天窗！\n";
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M41", "开启天窗", facNo.ToString());
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M50", "关闭白炽灯", facNo.ToString());
                    }
                }
                //土壤湿度判断
                if (soilHum > soilHumMax)
                {
                    //soilHumMsg = "，土壤湿度过高，温室过高就喷水开，风机开；否则喷水关风机开！\n";
                    if (temp > tempMax)
                    {
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M31", "开启水泵", facNo.ToString());
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M11", "开启风机", facNo.ToString());
                    }
                    else
                    {
                        //soilHumMsg = "，土壤湿度过高，风机开！\n";
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M11", "开启风机", facNo.ToString());
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M30", "关闭水泵", facNo.ToString());
                    }
                }
                if (soilHum < soilHumMin)
                {
                    //soilHumMsg = "，土壤湿度过低，喷水灌溉开！\n";
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M31", "开启水泵", facNo.ToString());
                    myserialPortSend.buildOrder(modeId, from, area.ToString(), "M10", "关闭风机", facNo.ToString());
                }
                if (soilHum >= soilHumMin && soilHum <= soilHumMax)
                {
                    //soilHumMsg = "，土壤湿度正常，如果温度高，则开启喷水，否则关闭风机！,关闭水泵！\n";
                    if (temp > tempMax)
                    {
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M31", "开启水泵", facNo.ToString());
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M10", "关闭风机", facNo.ToString());
                    }
                    else
                    {
                        //soilHumMsg = "，土壤湿度正常，关闭风机！,关闭水泵！\n";
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M10", "关闭风机", facNo.ToString());
                        myserialPortSend.buildOrder(modeId, from, area.ToString(), "M30", "关闭水泵", facNo.ToString());
                    }
                }
            }
            //MessageBox.Show(msg);
            //MessageBox.Show("area:" + area + " temp: " + temp + " hum: " + hum + " ill: " + ill + " soilhum: " + soilHum + " rain: " + rain + "\ntempMin: " + tempMin + " tempMax:" + tempMax + "\nhumMin: " + humMin + " humMax:" + humMax + "\nillMin: " + illMin + " illMax: " + illMax + "\nsoilHumMin:" + soilHumMin + " soilHumMax:" + soilHumMax + "\n" );
            // MessageBox.Show(tempMsg + humMsg + illMsg + soilHumMsg);
        }
    }
}
